package recommend;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.sun.org.apache.bcel.internal.generic.NEW;

import service.UserService;
import db.DBConn;
import entities.GoodsEntity;
import entities.UserEntity;

public class RecommendHelper {

	// 获得用户总数
	public static int userCount() {
		int count = 0;
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String sql = "select count(*) from shop_user ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		return count;
	}

	// 获得商品总数
	public static int goodsCount() {
		int count = 0;
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String sql = "select count(*) from shop_goods ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		return count;
	}

	// 得到所有的商品名
	public static String[] goodsName() {
		List<String> goodsNameList = new ArrayList<String>();
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String sql = "select goods_name from shop_goods ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				goodsNameList.add(rs.getString(1));
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		String[] goodsName = new String[goodsNameList.size()];
		for (int i = 0; i < goodsNameList.size(); i++) {
			goodsName[i] = goodsNameList.get(i);
		}
		return goodsName;
	}

	// 得到所有的商品名
	public static String[] userName() {
		List<String> userNameList = new ArrayList<String>();
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String sql = "select user_name from shop_user ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				userNameList.add(rs.getString(1));
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		String[] userName = new String[userNameList.size()];
		for (int i = 0; i < userNameList.size(); i++) {
			userName[i] = userNameList.get(i);
		}
		return userName;
	}

	// 得到所有的商品Id
	public static int[] goodsId() {
		List<Integer> goodsIdList = new ArrayList<Integer>();
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String sql = "select goods_id from shop_goods ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				goodsIdList.add(rs.getInt(1));
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		int[] goodsId = new int[goodsIdList.size()];
		for (int i = 0; i < goodsIdList.size(); i++) {
			goodsId[i] = goodsIdList.get(i);
		}
		return goodsId;
	}

	// 得到所有的用户Id
	public static int[] userId() {
		List<Integer> userIdList = new ArrayList<Integer>();
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			String sql = "select user_id from shop_user ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				userIdList.add(rs.getInt(1));
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		int[] userId = new int[userIdList.size()];
		for (int i = 0; i < userIdList.size(); i++) {
			userId[i] = userIdList.get(i);
		}
		return userId;
	}

	// 得到打分数据
	public static int[][] getScores() {
		int[] goodsIds = goodsId();
		int[] userIds = userId();
		int[][] scores = new int[userCount()][goodsCount()];
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			// 获得用户的打分数据
			String sql = "select comment_goodsId,comment_userId,comment_rank from shop_comment order by comment_userId ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int col = 0;
				int row = 0;
				int gid = rs.getInt(1);
				int uid = rs.getInt(2);
				int score = rs.getInt(3);

				for (int i = 0; i < goodsIds.length; i++) {
					if (gid == goodsIds[i]) {
						col = i;
					}
				}

				for (int i = 0; i < userIds.length; i++) {
					if (uid == userIds[i]) {
						row = i;
					}
				}

				scores[row][col] = score;
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return scores;
	}

	// 保存推荐数据
	public static int writeRecommend(String userName, String goodsName,
			double score) {
		Connection conn = DBConn.getConn();
		Statement stmt;
		int result = 0;
		try {
			UserEntity user = UserService.getUser(userName);
			stmt = conn.createStatement();

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			String sql = "insert into shop_recommend values(null,"
					+ user.getId() + ",'" + userName + "',null,'" + goodsName
					+ "'," + score + ",1,'" + sdf.format(new Date()) + "') ";

			if (haveRecommend(userName, goodsName)) {
				sql = "update shop_recommend set rcmd_count=rcmd_count+1 , rcmd_time = '"
						+ sdf.format(new Date())
						+ "'  where rcmd_userName like '"
						+ userName
						+ "'  and rcmd_goodsName like '" + goodsName + "' ";
			}
			System.out.println(sql);
			result = stmt.executeUpdate(sql);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		return result;
	}

	// 判断某个商品是否已推荐给某个用户
	private static boolean haveRecommend(String userName, String goodsName) {
		Connection conn = DBConn.getConn();
		Statement stmt;
		boolean result = false;
		try {
			stmt = conn.createStatement();

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			String sql = "select * from shop_recommend where rcmd_userName like '"
					+ userName
					+ "'  and rcmd_goodsName like '"
					+ goodsName
					+ "' ";
			// System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			result = rs.next();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}

	public static List<GoodsEntity> getRecommendGoods(String userName, int limit) {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			if (userName == null) {
				userName = "%";
			}
			String sql = "select * from shop_recommend,shop_goods where rcmd_goodsName = goods_name and rcmd_userName like '"
					+ userName
					+ "' order by rcmd_score desc,rcmd_count desc,rcmd_time desc limit "
					+ limit;

			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				GoodsEntity goods = new GoodsEntity();
				goods.setId(rs.getInt("goods_id"));
				goods.setClassId(rs.getInt("goods_classId"));
				goods.setName(rs.getString("goods_name"));
				goods.setSellerId(rs.getInt("goods_sellerId"));
				goods.setSaleClass(rs.getInt("goods_saleClass"));
				goods.setPrice(rs.getDouble("goods_price"));
				goods.setDescrition(rs.getString("goods_desc"));
				goods.setDiscount(rs.getDouble("goods_discount"));
				goods.setImageUrl(rs.getString("goods_image"));
				goods.setRank(rs.getDouble("goods_rank"));
				goods.setSaleCount(rs.getInt("goods_saleCount"));
				SimpleDateFormat sdf = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				goods.setOnSaleTime(sdf.format(rs
						.getTimestamp("goods_onSaleTime")));

				goodsList.add(goods);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return goodsList;

	}

	public static void main(String[] args) {
		System.out.println(getRecommendGoods("liwentao",2));
	}
}
